/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import pojo.Foreignkey;
import pojo.Property;
import pojo.Table;
import java.util.ArrayList;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 *
 * @author Snowflake
 */
public class ExecTableXML {
    //Add thẻ name vào Database

    public static boolean addNameToDatabase(Document doc, String Name) {
        boolean result = true;
        try {
            Element name = doc.createElement("name");
            name.setTextContent(Name.toLowerCase());
            Element root = doc.getDocumentElement();
            root.appendChild(doc.createTextNode("\t"));
            root.appendChild(name);
            root.appendChild(doc.createTextNode("\n"));
            ExecXML.saveXmlFile(doc, getNameDatabase(doc) + ".xml");
        } catch (Exception e) {
            result = false;
            e.printStackTrace();
        }
        return result;
    }

    /*
     * Ham nay kiem tra 1 ten property co ton tai chua, lưu ý lay tat ca tên id và thuộc tính
     */
    public static boolean isExistedFieldName(Document doc, String tableName, String PropertyName) {
        try {
            ArrayList<Property> ids = ExecIdXML.getIdsOfTable(doc, tableName);
            ArrayList<Property> properties = ExecPropertyXML.getPropertyOfTable(doc, tableName);
            ArrayList<String> allPropertyNames = new ArrayList<String>();
            if (ids != null && !ids.isEmpty()) {
                for (int i = 0; i < ids.size(); i++) {
                    allPropertyNames.add(ids.get(i).getName());
                }
            }
            if (properties != null && !properties.isEmpty()) {
                for (int i = 0; i < properties.size(); i++) {
                    allPropertyNames.add(properties.get(i).getName());
                }
            }
            return ExecXML.isExistedString(allPropertyNames, PropertyName);
        } catch (Exception ex) {
            return true;
        }
    }
    //ham đi  hàm trên

    public static boolean isExistedFieldName(String databaseName, String tableName, String PropertyName) {
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return isExistedFieldName(doc, tableName, PropertyName);
        } catch (Exception ex) {
            return true;
        }
    }
    /*
     * Add Table vào database
     * 
     */

    public static boolean addTableToDatabase(String databaseName, Table table) {
        boolean result = true;
        databaseName = databaseName.trim();
        String tableName = table.getName().trim();
        if (isExistedTable(databaseName, tableName)) {
            return result = false;
        }
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            ArrayList<Property> ids = table.getIdList();
            ArrayList<Property> properties = table.getPropertyList();
            ArrayList<Foreignkey> foreignkeys = table.getForeignKeyList();

            if ((ids != null && !ids.isEmpty()) || (properties != null && !properties.isEmpty())
                    || (foreignkeys != null && !foreignkeys.isEmpty())) {
                Element name = doc.createElement("table");
                name.setAttribute("name", table.getName().toLowerCase());
                Element root = doc.getDocumentElement();
                root.appendChild(doc.createTextNode("\t"));
                root.appendChild(name);
                root.appendChild(doc.createTextNode("\n"));
                ExecXML.saveXmlFile(doc, databaseName + ".xml");
            } else {
                return result = false;
            }
            if (ids != null && !ids.isEmpty()) {
                for (int i = 0; i < ids.size(); i++) {
                    ExecIdXML.addIDToTable(doc, tableName, ids.get(i));
                }
            }
            if (properties != null && !properties.isEmpty()) {
                for (int i = 0; i < properties.size(); i++) {
                    ExecPropertyXML.addPropertyToTable(doc, tableName, properties.get(i));
                }
            }
            if (foreignkeys != null && !foreignkeys.isEmpty()) {
                for (int i = 0; i < foreignkeys.size(); i++) {
                    ExecForeignkeyXML.addFkToTable(doc, tableName, foreignkeys.get(i));
                }
            }
            ExecXML.saveXmlFile(doc, databaseName + ".xml");
        } catch (Exception e) {
            result = false;
            e.printStackTrace();
        }
        return result;
    }

    //Chuyển kiểu Unique
    public static String convertUnique(Property pt) {
        String kq;
        if (pt.isUnique() == true) {
            kq = "Yes";
        } else {
            kq = "No";
        }
        return kq;
    }
    //Chuyển kiểu notnull

    public static String convertNotnull(Property pt) {
        String kq;
        if (pt.isNotnull() == true) {
            kq = "Yes";
        } else {
            kq = "No";
        }
        return kq;
    }

    public static boolean convertElementNotnull(Element pt) {
        boolean kq;
        if (pt.getAttribute("notnull").equalsIgnoreCase("Yes")) {
            kq = true;
        } else {
            kq = false;
        }
        return kq;
    }

    public static boolean convertElementUnique(Element pt) {
        boolean kq;
        if (pt.getAttribute("unique").equalsIgnoreCase("Yes")) {
            kq = true;
        } else {
            kq = false;
        }
        return kq;
    }

    // Hàm này lấy danh sách các tên table trong 1 database
    public static ArrayList<String> getNamesOfTable(Document doc) {
        ArrayList<String> tableNames = new ArrayList<String>();
        try {
            doc.getDocumentElement().normalize();
            NodeList nodeList = doc.getElementsByTagName("table");
            for (int i = 0; i < nodeList.getLength(); i++) {
                Node node = nodeList.item(i);
                if (node.getNodeType() == Node.ELEMENT_NODE) {
                    if (node.hasAttributes()) {
                        Element ele = (Element) node;
                        String str = ele.getAttribute("name");
                        tableNames.add(str);
                    }
                }
            }
            if (tableNames.isEmpty()) {
                tableNames = null;
            }
        } catch (Exception e) {
            tableNames = null;
            e.printStackTrace();
        }
        return tableNames;
    }

    public static ArrayList<String> getNamesOfTable(String databaseName) {
        databaseName = databaseName.trim();
        Document doc = ExecXML.getDocument(databaseName + ".xml");
        return getNamesOfTable(doc);
    }
    /*
     * Hàm này lấy thông tin 1 bảng từ database
     */

    public static Table getTableFromDatabase(Document doc, String tableName) {
        Table tables = new Table();
        try {
            doc.getDocumentElement().normalize();
            NodeList nl = doc.getElementsByTagName("table");
            Node node = null;
            Element tb = null;
            for (int i = 0; i < nl.getLength(); i++) {
                node = nl.item(i);
                if (node.getNodeType() == Node.ELEMENT_NODE) {
                    tb = (Element) node;
                    if (tb.getAttribute("name").equalsIgnoreCase(tableName)) {
                        tables.setName(tableName);
                        tables.setIdList(ExecIdXML.getIdsOfTable(doc, tableName));
                        tables.setPropertyList(ExecPropertyXML.getPropertyOfTable(doc, tableName));
                        tables.setForeignKeyList(ExecForeignkeyXML.getForeignkeyOfTable(doc, tableName));
                    }
                }
            }
        } catch (Exception e) {
            tables = null;
            e.printStackTrace();
        }
        return tables;
    }

    public static Table getTableFromDatabase(String databaseName, String tableName) {
        try {
            databaseName = databaseName.trim();
            tableName = tableName.trim();
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return getTableFromDatabase(doc, tableName);
        } catch (Exception ex) {
            return null;
        }
    }
    /*
     * Hàm này lấy tất cả thông tin các bảng của database
     */

    public static ArrayList<Table> getTablesFromDatabase(Document doc) {
        ArrayList<Table> tables = new ArrayList<Table>();
        try {
            doc.getDocumentElement().normalize();
            NodeList nl = doc.getElementsByTagName("table");
            Node node = null;
            Element tb = null;
            for (int i = 0; i < nl.getLength(); i++) {
                node = nl.item(i);
                if (node.getNodeType() == Node.ELEMENT_NODE) {
                    tb = (Element) node;
                    Table table = new Table();
                    String tableName = tb.getAttribute("name");
                    table.setName(tableName);
                    table.setIdList(ExecIdXML.getIdsOfTable(doc, tableName));
                    table.setPropertyList(ExecPropertyXML.getPropertyOfTable(doc, tableName));
                    table.setForeignKeyList(ExecForeignkeyXML.getForeignkeyOfTable(doc, tableName));
                    tables.add(table);
                }
            }
            if (tables.isEmpty()) {
                tables = null;
            }
        } catch (Exception e) {
            tables = null;
            e.printStackTrace();
        }
        return tables;
    }

    /*
     * Ham nay kiem tra 1 ten table co ton tai chua
     */
    public static boolean isExistedTable(String databaseName, String tableName) {
        try {
            databaseName = databaseName.trim();
            tableName = tableName.trim();
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            ArrayList<String> tableNames = getNamesOfTable(doc);
            return ExecXML.isExistedString(tableNames, tableName);
        } catch (Exception ex) {
            return true;
        }
    }

    public static boolean isExistedTable(Document doc, String tableName) {
        try {
            tableName = tableName.trim();
            ArrayList<String> tableNames = getNamesOfTable(doc);
            return ExecXML.isExistedString(tableNames, tableName);
        } catch (Exception ex) {
            return true;
        }
    }

    /*
     * Hàm này lấy một field trong table( co the id hoặc property)
     */
    public static Property getFieldOfTable(Document doc, String tableName, String fieldName) {
        tableName = tableName.toLowerCase().trim();
        fieldName = fieldName.toLowerCase().trim();
        Property field = new Property();
        try {
            doc.getDocumentElement().normalize();
            String xPathId = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tableName + "']/id[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + fieldName + "']";
            String xPathPro = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tableName + "']/property[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + fieldName + "']";

            Node idNode = (Node) ExecXML.execXpathReturnNode(doc, xPathId);
            Node proNode = (Node) ExecXML.execXpathReturnNode(doc, xPathPro);
            Element element = null;
            if (idNode != null || proNode != null) {
                if (idNode != null) {
                    element = (Element) idNode;
                } else {
                    element = (Element) proNode;
                }
                field.setName(element.getAttribute("name"));
                field.setLength(Integer.parseInt(element.getAttribute("length")));
                field.setType(element.getAttribute("type"));
                field.setNotnull(ExecTableXML.convertElementNotnull(element));
                field.setUnique(ExecTableXML.convertElementUnique(element));
            } else {
                field = null;
            }
        } catch (Exception e) {
            field = null;
            e.printStackTrace();
        }
        return field;
    }

    public static Property getFieldOfTable(String databaseName, String tableName, String fieldName) {
        databaseName = databaseName.toLowerCase().trim();
        tableName = tableName.toLowerCase().trim();
        fieldName = fieldName.toLowerCase().trim();
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return getFieldOfTable(doc, tableName, fieldName);
        } catch (Exception ex) {
            return null;
        }
    }
    /*
     * Hàm này lấy tên database của document tương ứng
     */

    public static String getNameDatabase(Document doc) {
        String str = null;
        try {
            NodeList nl = doc.getElementsByTagName("name");
            for (int i = 0; i < nl.getLength(); i++) {
                Node node = nl.item(i);
                Element name = (Element) node;
                str = name.getTextContent().toString();
            }
        } catch (Exception ex) {
            str = null;
        }
        return str;
    }

    public static boolean delTableFromDatabase(Document doc, String tableName) {
        boolean result = true;
        if (!isExistedTable(doc, tableName)) {
            return result = false;
        }
        //Lấy hết tất cả danh sánh foreignkey trong database chứa table có name = tableName
        doc.getDocumentElement().normalize();
        NodeList nl = doc.getElementsByTagName("foreignkey");
        for (int i = 0; i < nl.getLength(); i++) {
            Node node = nl.item(i);
            Element e = (Element) node;
            // Nếu có fk nào có thuộc tính tableref chứa tableName thì ko cho xóa table.
            if (e.getAttribute("tableref").equalsIgnoreCase(tableName)) {
                return result = false;
            }
        }
        try {
            //Lấy table có name = tableName
            String xPath = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tableName + "']";
            Node tb = (Node) ExecXML.execXpath(doc, xPath, "node");
            tb.getParentNode().removeChild(tb);
            ExecXML.saveXmlFile(doc, getNameDatabase(doc) + ".xml");
        } catch (Exception e) {
            result = false;
            e.printStackTrace();
        }
        return result;
    }

    /*
     * Hàm này lấy thông tin tất cả các field
     */
    public static ArrayList<String> getAllFieldsFromTable(Document doc, String tableName) {
        ArrayList<String> kq = new ArrayList<String>();
        try {
            ArrayList<Property> idList = ExecIdXML.getIdsOfTable(doc, tableName);
            ArrayList<Property> ptList = ExecPropertyXML.getPropertyOfTable(doc, tableName);
            if (idList != null && !idList.isEmpty()) {
                for (int i = 0; i < idList.size(); i++) {
                    Property id = idList.get(i);
                    kq.add(id.getName());
                }
            }
            if (ptList != null && !ptList.isEmpty()) {
                for (int i = 0; i < ptList.size(); i++) {
                    Property pt = ptList.get(i);
                    kq.add(pt.getName());
                }
            }
        } catch (Exception e) {
            kq = null;
            e.printStackTrace();
        }
        return kq;
    }

    public static ArrayList<String> getAllFieldsFromTable(String databaseName, String tableName) {
        try {
            databaseName = databaseName.trim();
            tableName = tableName.trim();
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return getAllFieldsFromTable(doc, tableName);
        } catch (Exception ex) {
            return null;
        }
    }
    /*
     * Hàm này lấy tên các table có thẻ id
     */

    public static ArrayList<String> getNameOfTableHasID(String databaseName) {
        databaseName = databaseName.trim();
        Document doc = ExecXML.getDocument(databaseName + ".xml");
        ArrayList<String> kq = new ArrayList<String>();
        ArrayList<Table> tb = ExecTableXML.getTablesFromDatabase(doc);
        try {
            for (int i = 0; i < tb.size(); i++) {
                String xPath = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tb.get(i).getName() + "']/id";
                NodeList idList = (NodeList) ExecXML.execXpath(doc, xPath, "nodeset");
                if (idList.getLength() != 0) {
                    kq.add(tb.get(i).getName());
                }
                idList = null;
            }
        } catch (Exception e) {
            kq = null;
            e.printStackTrace();
        }
        return kq;
    }
}
